using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Collections;

namespace Deepcode.Flux.Core.Data
{
	/// <summary>
	/// Provides utilities for working with batches of SQL statements in files
	/// </summary>
	public class SQLBatchProcessor
	{
		#region Public Methods
		/// <summary>
		/// Reads the SQL File specified and executes it, optionally in a transaction.
		/// Individual SQL Commands are split with [GO]
		/// </summary>
		/// <param name="SQLFilename"></param>
		public void ExecuteQueryScriptSQLFile(string SQLFilename, SqlConnection c, SqlTransaction t)
		{
			ExecuteQueryScriptSQLFile(SQLFilename, "[GO]", c, t);
		}
		/// <summary>
		/// Reads the SQL File specified and executes it, optionally in a transaction.
		/// Individual SQL Commands are split on the commandIdentifier parameter
		/// </summary>
		/// <param name="SQLFilename"></param>
		/// <param name="commandIdentifier"></param>
		public void ExecuteQueryScriptSQLFile(string SQLFilename, string commandIdentifier, SqlConnection c, SqlTransaction t)
		{
			// Read the entire file into a string variable
			StreamReader r = new StreamReader(SQLFilename);
			string Script = r.ReadToEnd();
			r.Close();

			ExecuteQueryScriptSQL(Script, commandIdentifier, c, t);
		}
		#endregion
		#region Private methods
		/// <summary>
		/// Executes the SQL Source specified against the database. Each SQL
		/// Command is seperated with commandIdentifier. Optionally the
		/// SQL Batch can be executed in one large transaction providing rollback
		/// support should any individual command fail.
		/// </summary>
		/// <param name="SQLSource"></param>
		/// <param name="UseTransaction"></param>
		/// <param name="commandIdentifier"></param>
		private void ExecuteQueryScriptSQL(string SQLSource, string commandIdentifier, SqlConnection c, SqlTransaction t)
		{
			string[] Commands = this.SplitSQLBatchOn(SQLSource, commandIdentifier);
			foreach (string Command in Commands)
			{
				SqlCommand cmd = new SqlCommand(Command, c, t);
				cmd.ExecuteNonQuery();
			}
		}
		#region Batch Splitter
		/// <summary>
		/// Splits an SQL Batch into it's individual commands.
		/// </summary>
		/// <param name="ScriptSource">The SQL Batch source</param>
		/// <param name="commandID">The identifier to split the commands on</param>
		/// <returns>An array of strings representing each command to execute</returns>
		private string[] SplitSQLBatchOn(string ScriptSource, string commandID)
		{
			ArrayList strings = new ArrayList();
			int commandLen = commandID.Length;
			// scan through Text for [GO]'s
			int position = ScriptSource.IndexOf(commandID);
			int lastPosition = 0;

			// Check for no [GO] statements - return entire script
			if (position == -1)
			{
				string[] r = new string[1];
				r[0] = ScriptSource;
				return r;
			}

			while (position != -1)
			{
				string thisScript = ScriptSource.Substring(lastPosition, (position - lastPosition));

				if (thisScript != "")
					strings.Add(thisScript);

				lastPosition = position + commandLen;
				position = ScriptSource.IndexOf(commandID, lastPosition);
				if (position == -1)
				{
					if (lastPosition < ScriptSource.Length)
						strings.Add(ScriptSource.Substring(lastPosition));
				}
			}

			return strings.ToArray(typeof(string)) as string[];
		}
		#endregion
		#endregion
	}
}
